# -*- coding: utf-8 -*-
# all the imports
import os
import sys
reload(sys)
sys.setdefaultencoding('utf8')
from flask import Flask,request,session,g,redirect,url_for,abort,render_template,flash
from contextlib import closing
import pyodbc


from datetime import datetime
import time

import json
#create our little application
app=Flask(__name__)




app.config.update(dict(
    DATABASE="TradeRecord",
    DEBUG=True,
    SECRET_KEY='development key',
    USERNAME='lyc',
    PASSWORD='p0o9i8u7',
    ALLFORM1="mock",
    ALLFORM2="statement(date)",
    ALLFORM3="statement(name)",
    ALLFORM4="out_summary statement",
    ALLFORM5="candlestick"
    ))
client1 = pyodbc.connect('DRIVER={SQL Server};SERVER=120.24.68.150,1453;DATABASE=outname;UID=dbUser;PWD=db+123-456')
outname = client1.cursor()

client2 = pyodbc.connect('DRIVER={SQL Server};SERVER=120.24.68.150,1453;DATABASE=outweb;UID=dbUser;PWD=db+123-456')
outweb = client2.cursor()
sheetname=outweb.execute("select date from [statement(name)]").fetchall()



@app.route('/')
def first_page():
    

    return render_template('layout.html')
@app.route('/show1/<k>')
def show_form1(k):
    closeprice=[]
    timecollect=[]
    profit=[]
    net=[]
    duo=[]
    kong=[]
    count=[]
    cur=outweb.execute('select * from [{0}] order by date'.format(k))
    entries=[dict(date=row[0].decode("gbk"),pingprofit=int(row[1]),volume=int(row[2]),maxduo=int(row[3]),maxkong=int(row[4]),maxprofit=int(row[5]),minprofit=int(row[6]),count=row[7])for row in cur.fetchall()]
    for i in entries:
        timecollect.append(i["date"])
        profit.append(int(i["pingprofit"]))
        net.append(int(i["volume"]))
        duo.append(int(i["maxduo"]))
        kong.append(int(i["maxkong"]))
        count.append(int(i["count"]))
        closeprice.append([str(i["date"]),0,int(i["pingprofit"]),int(i["minprofit"]),int(i["maxprofit"])])
    return render_template('show_form1.html',entries=entries,k=k,timecollect=json.dumps(timecollect),profit=profit,net=net,duo=duo,kong=kong,count=count,closeprice=closeprice)
@app.route('/show2/<k>')
def show_form2(k):
    closeprice=[]
    timecollect=[]
    profit=[]
    net=[]
    duo=[]
    kong=[]
    count=[]
    cur=outweb.execute('select * from [{0}] order by date'.format(k))
    entries=[dict(date=row[0].decode("gbk"),pingprofit=int(row[1]),volume=int(row[2]),maxduo=int(row[3]),maxkong=int(row[4]),maxprofit=int(row[5]),minprofit=int(row[6]),count=row[7])for row in cur.fetchall()]
    for i in entries:
        timecollect.append(i["date"])
        profit.append(int(i["pingprofit"]))
        net.append(int(i["volume"]))
        duo.append(int(i["maxduo"]))
        kong.append(int(i["maxkong"]))
        count.append(int(i["count"]))
        closeprice.append([str(i["date"]),0,int(i["pingprofit"]),int(i["minprofit"]),int(i["maxprofit"])])
    return render_template('show_form2.html',entries=entries,k=k,timecollect=json.dumps(timecollect),profit=profit,net=net,duo=duo,kong=kong,count=count,closeprice=closeprice)
@app.route('/showname/<k>',methods=['GET'])
def show_form3(k):
    cur=outname.execute('select * from [{0}]'.format(k))
    entries=[dict(timedate=row[0].decode("gbk"),result=int(row[1]),poundage=int(row[2]))for row in cur.fetchall()]
    return render_template('show_form3.html',entries=entries,k=k)
@app.route('/show6/<p>/<s>/<e>')
def show_form6(p,s,e):
    if p=="all":
        cur=outweb.execute("select * from [statement(name)]order by date")
        entries=[dict(date=row[0].decode("gbk"),pingprofit=int(row[1]),volume=row[2],maxduo=row[3],maxkong=row[4],maxprofit=int(row[5]),minprofit=int(row[6]),count=row[7],start=row[8].decode("gbk"),end=row[9].decode("gbk"),tradeplace=row[10].decode("gbk"),instrument=row[11],simulatedprofit=row[12],simulatedvolume=row[13],simulateddaycount=row[14],actualprofit=row[15],actualvolume=row[16],actualdaycount=row[17])for row in cur.fetchall()]
        
        return render_template('show_form6.html',entries=entries)
    else:
        cur=outweb.execute("select * from [statement(name)] where [date]='{0}' order by date".format(p.encode("gbk")))
        entries=[dict(date=row[0].decode("gbk"),pingprofit=int(row[1]),volume=row[2],maxduo=row[3],maxkong=row[4],maxprofit=int(row[5]),minprofit=int(row[6]),count=row[7],start=row[8].decode("gbk"),end=row[9].decode("gbk"),tradeplace=row[10].decode("gbk"),instrument=row[11],simulatedprofit=row[12],simulatedvolume=row[13],simulateddaycount=row[14],actualprofit=row[15],actualvolume=row[16],actualdaycount=row[17])for row in cur.fetchall()]
        cur=outname.execute("select * from [{0}] where timedate between '{1}' and '{2}' order by timedate".format(p.encode("gbk"),s,e))
        newentries=[dict(timedate=row[0].decode("gbk"),result=int(row[1]))for row in cur.fetchall()]
        return render_template('show_form6.html',entries=entries,newentries=newentries)
@app.route('/show4/<s>/<e>')
def show_form4(s,e):
    timecollect=[]
    profit=[]
    net=[]
    duonet=[]
    kongnet=[]
    duo=[]
    kong=[]
    cur=outweb.execute("select * from [out_summary statement] where timedate between '{0}' and '{1}' order by timedate".format(s,e))
    entries=[dict(timedate=row[0].decode("gbk"),price=int(row[1]),result=int(row[2]),net=int(row[3]),poundage=int(row[4]),pureresult=int(row[5]),duonet=int(row[6]),duomovement=int(row[7]),kongnet=int(row[8]),kongmovement=int(row[9]))for row in cur.fetchall()]
    for i in entries:
        timecollect.append(i["timedate"])
        profit.append(int(i["result"]-entries[0]["result"]))
        net.append(int(i["net"]))
        duonet.append(int(i["duonet"]))
        kongnet.append(int(i["kongnet"]))
        duo.append(int(i["duomovement"]-entries[0]["duomovement"]))
        kong.append(int(i["kongmovement"]-entries[0]["kongmovement"]))
    
    return render_template('show_form4.html',entries=entries,timecollect=json.dumps(timecollect),profit=profit,net=net,duonet=duonet,kongnet=kongnet,duo=duo,kong=kong)
@app.route('/show5/<s>/<e>')
def show_form5(s,e):
    cur=outweb.execute("select * from [statement(date)] where date between '{0}' and '{1}' order by date".format(s,e))
    closeprice=[]
    entries=[dict(date=row[0].decode("gbk"),pingprofit=row[1],volume=row[2],maxduo=row[3],maxkong=row[4],maxprofit=row[5],minprofit=row[6],count=row[7])for row in cur.fetchall()]

    for i in entries:
        closeprice.append([str(i["date"]),0,int(i["pingprofit"]),int(i["minprofit"]),int(i["maxprofit"])])

    return render_template('show_form5.html',entries=entries,closeprice=closeprice)
@app.route('/add',methods=['POST'])
def add_entry():
    
    if not session.get('logged_in'):
        abort(401)
    post = {"title":request.form['title'],"text":request.form['text']}
    g.tr.posts.insert(post)

    flash('New entry was successfully posted')
    return redirect(url_for('show_entries'))


@app.route('/logout')
def logout():
    session.pop('logged_in', None)
    
    return redirect(url_for('first_page'))
@app.route('/choose1',methods=['GET','POST'])
def choose1():
    error=None
    if request.method=='POST':
        if  request.form['formname1']=='total':
            if request.form['formname2']=='all':
                return redirect(url_for('show_form1',k='statement(date)'))
            elif request.form['formname2']=='mock':
                return redirect(url_for('show_form2',k='statement(mock)'))
            else:
                return redirect(url_for('show_form2',k='statement(actual)'))
        elif request.form['formname1']=='longhua':
            if request.form['formname2']=='all':
                return redirect(url_for('show_form2',k='statement(lh)'))
            elif request.form['formname2']=='mock':
                return redirect(url_for('show_form2',k='statement(mocklh)'))
            else:
                return redirect(url_for('show_form2',k='statement(actuallh)'))
        elif request.form['formname1']=='shajing':
            if request.form['formname2']=='all':
                return redirect(url_for('show_form2',k='statement(sj)'))
            elif request.form['formname2']=='mock':
                return redirect(url_for('show_form2',k='statement(mocksj)'))
            else:
                return redirect(url_for('show_form2',k='statement(actualsj)'))
        elif request.form['formname1']=='bantian':
            if request.form['formname2']=='all':
                return redirect(url_for('show_form2',k='statement(bt)'))
            elif request.form['formname2']=='mock':
                return redirect(url_for('show_form2',k='statement(mockbt)'))
            else:
                return redirect(url_for('show_form2',k='statement(actualbt)'))
        else:
            error='Invalid form'
    return render_template('chooseform1.html',error=error)
@app.route('/choose2',methods=['GET','POST'])
def choose2():
    error=None
    if request.method=='POST':
        return redirect(url_for('show_form6',p=request.form['whichname'],s=request.form['start'],e=request.form['end']))

    return render_template('chooseform.html',error=error,sheetname=sheetname)
@app.route('/choose3',methods=['GET','POST'])
def choose3():
    error=None
    if request.method=='POST':
        return redirect(url_for('show_form4',s=request.form['start'],e=request.form['end']))

    return render_template('chooseform2.html',error=error)
@app.route('/choose4',methods=['GET','POST'])
def choose4():
    error=None
    if request.method=='POST':
        return redirect(url_for('show_form5',s=request.form['start'],e=request.form['end']))
    return render_template('chooseform3.html',error=error,sheetname=sheetname)
def kline(closeprice):
    mondays = WeekdayLocator(MONDAY)     
    alldays    = DayLocator()             
    weekFormatter = DateFormatter('%b %d')  
    dayFormatter = DateFormatter('%d')      


    Prices =closeprice

    fig, ax = plt.subplots()
    fig.subplots_adjust(bottom=0.2)
    ax.xaxis.set_major_locator(mondays)
    ax.xaxis.set_minor_locator(alldays)
    ax.xaxis.set_major_formatter(weekFormatter)
    _candlestick(ax, Prices, width=0.6,colorup='r',colordown='g')

    ax.xaxis_date()
    ax.autoscale_view()
    plt.setp( plt.gca().get_xticklabels(), rotation=45, horizontalalignment='right')
    plot()
    savefig('D:/flaskrdata/static/6.png')
if __name__=='__main__':
    app.run(host='127.0.0.1',port=90)
